package model.DbAccess;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import controller.main.Executer;
import model.dao.CountryDao;



public class ConnectionManager 
{
	public static final String PROPERTY_FILE_PATH="/controller/DBconfiguration.properties";
	
	public Connection getConnection()
	{
		
		Properties prop=new Properties();
		Connection conn=null;
		try {
			//prop.load(CountryDao.class.getResourceAsStream(PROPERTY_FILE_PATH));
			prop.load(new FileInputStream(Executer.getJarDirectory()+File.separator+"DBconfiguration.properties"));
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String ip=prop.getProperty("ip");
		String port=prop.getProperty("port");
		String userName=prop.getProperty("username");
		String password=prop.getProperty("password");
		//String connectionName=prop.getProperty("connectionName");
		String sid=prop.getProperty("SID");
		int portNumber=Integer.parseInt(port);
		String url = "jdbc:oracle:thin:@" + ip + ":" + portNumber + ":" + sid;

		try{
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		conn = DriverManager.getConnection(url, userName, password);
    	System.out.println("Conn: "+conn);
		}
		catch(Exception e){
			e.printStackTrace();
			System.out.println("CANT CONNECT");
		}

		return conn;
	}
	
	public Boolean closeConnection(Connection conn)
	{
		Boolean flag=true;
		try {
			if(conn!=null){
				conn.close();	
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("CANT CLOSE CONNECTION");
			flag=false;
		}
		return flag;
		
	}
	
	/* public  void logCalller(String logID,String event,String receiveDate,String city,String description)
	{
		CallableStatement callStmnt=null;
		String callProcedure="{call nody(?,?,?,?,?)}";
		Connection connection = null ;
		try {
			 connection = getConnection();
			callStmnt = connection.prepareCall(callProcedure);
			callStmnt.setString(1, logID);
			callStmnt.setString(2, event);
			callStmnt.setString(3, receiveDate);
			callStmnt.setString(4, city);
			callStmnt.setString(5, description);
			callStmnt.execute();
			callStmnt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			closeConnection(connection);
		}
		
	}
	*/
	
	public void callDBToLoadFiles(String country,String interfaceName,String fileName)
	{
		CallableStatement callStmnt=null;
		Connection connection = getConnection();
		String callProcedure="{call chub_SQL_LOADER_cntry(?,?,?,?)}";
		try {
			callStmnt = connection.prepareCall(callProcedure);
			callStmnt.setString(1, country);
			callStmnt.setInt(2, 0);
			callStmnt.setString(3, interfaceName);
			callStmnt.setString(4, fileName);

			callStmnt.execute();
			callStmnt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			//logCalller(connection, null, null, null, country,e.getMessage() );
		}
		finally{
			closeConnection(connection);
		}
		
	}
	
	
	public void sendNotifications(String country,String interfaceName)
	{
		CallableStatement callStmnt=null;
		Connection connection = getConnection();
		String callProcedure="{call SEND_NOTIFICATION_MAILS(?,?)}";
		try {
			callStmnt = connection.prepareCall(callProcedure);
			callStmnt.setString(1, country);
			callStmnt.setString(2, interfaceName);

			callStmnt.execute();
			callStmnt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			//logCalller(connection, null, null, null, country,e.getMessage() );
		}
		finally{
			closeConnection(connection);
		}
	}
	
	
	
}







